﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using SPC.Properties;
using System.Collections;

namespace SPC.Classes
{
    class PersonEvents
    {
        /// <summary>
        /// Static string that stores the database table name.
        /// </summary>
        public static string PERSONS_TABLE = "Persons";

        /// <summary>
        /// Default constructor.
        /// </summary>
        public PersonEvents() { }

        /// <summary>
        /// Public class that gets the collection of Person object based on a series of conditions that can be set.
        /// </summary>
        public class GetCollection : IBasicRequest
        {
            /// <summary>
            /// Private person object used to store Persons attributes in order to use them as where conditions.
            /// </summary>
            private Person objPerson;

            /// <summary>
            /// Flag used to decide if the related Persons penalties should be brought from databse or not.
            /// </summary>
            private bool boolIncludePenalties;

            /// <summary>
            /// Class used to get a collection of persons based on the set conditions. 
            /// After conditions are set, use Request method to retrive data.
            /// </summary>
            public GetCollection()
            {
                this.objPerson = new Person();
                this.boolIncludePenalties = false;
            }

            /// <summary>
            /// Condition setter for the Request based on the ID.
            /// </summary>
            /// <param name="value">ID of the person that should be placed on where condition.</param>
            public void setID(string value)
            {
                this.objPerson.setID(value);
            }
            public string getID()
            {
                return this.objPerson.getID();
            }

            public void setName(string value)
            {
                this.objPerson.setName(value);
            }
            public string getName()
            {
                return this.objPerson.getName();
            }

            public void setAddress(string value)
            {
                this.objPerson.setAddress(value);
            }
            public string getAddress()
            {
                return this.objPerson.getAddress();
            }

            public void setIncludePenalties()
            {
                this.boolIncludePenalties = true;
            }
            public void setIncludePenalties(bool value)
            {
                this.boolIncludePenalties = value;
            }
            public bool getIncludePenalties()
            {
                return this.boolIncludePenalties;
            }


            #region IBasicRequest Members

            public IList Request()
            {
                try
                {
                    SQLiteDatabase db = new SQLiteDatabase();
                    DataTable person = new DataTable();
                    String query = "select *";
                    query += " from " + PERSONS_TABLE;

                    Dictionary <string,string> dictData = this.objPerson.getDictData();

                    //Here apply all arrData from the private object for where conditions
                    if (dictData.Count > 0)
                    {
                        query += " where ";
                        int intCounter = dictData.Count;
                        foreach (KeyValuePair<string, string> objDictItemPersonAttr in dictData)
                        {
                            query += objDictItemPersonAttr.Key + "='" + objDictItemPersonAttr.Value + "'";
                            intCounter--;
                            if(intCounter>0) query += " and ";
                        }
                        query += ";";
                    }
                    else
                    {
                        query += ";";
                    }

                    MessageBox.Show(query);

                    person = db.GetDataTable(query);


                    List<Person> listPersons = new List<Person>();
                    // Or looped through for some other reason
                    foreach (DataRow r in person.Rows)
                    {
                        Person objPerson = new Person(r);
                        if (this.boolIncludePenalties)
                        {
                            //bring also the penalties and assigne them to the person object.
                            PenaltyEvents evntPenalty = new PenaltyEvents();
                            objPerson.setPenalties( evntPenalty.GetAllPenalties4Person(int.Parse(objPerson.getID())) );
                        }
                        listPersons.Add(objPerson);
                    }


                    return listPersons;
                }
                catch (Exception fail)
                {
                    String error = "The following error has occurred:\n\n";
                    error += fail.Message.ToString() + "\r\n";
                    error += fail.StackTrace.ToString() + "\r\n";
                    MessageBox.Show(error);
                    return null;
                }
            }

            #endregion
        };

        public class Get : GetCollection
        {
            public Get() { }

            public new Person Request()
            {
                List<Person> listPersons = (List<Person>)base.Request();
                if (listPersons.Count > 0)
                    return listPersons[0];
                else
                    return null;
            }
        };

        public List<Person> GetAllPersons()
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable person = new DataTable();
                String query = "select *";
                query += " from " + PERSONS_TABLE + ";";
                person = db.GetDataTable(query);
                // The results can be directly applied to a DataGridView control
                //personDataGrid.DataSource = person;
                

                List<Person> listPersons = new List<Person>();
                // Or looped through for some other reason
                foreach (DataRow r in person.Rows)
                {
                    Person objPerson = new Person();
                    objPerson.setID(r["id"].ToString());
                    objPerson.setName(r[Properties.Settings.Default.PERSON_ATTRIB_NAME].ToString());
                    objPerson.setAddress(r[Properties.Settings.Default.PERSON_ATTRIB_ADDRESS].ToString());
                    listPersons.Add(objPerson);
                }


                return listPersons;
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public Person GetAllPerson(int intPersonID)
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable person = new DataTable();
                String query = "select *";
                query += " from " + PERSONS_TABLE;
                if (intPersonID > 0) query += " where id = " + intPersonID + ";";
                person = db.GetDataTable(query);
                // The results can be directly applied to a DataGridView control

                List<Person> listPersons = new List<Person>();
                // Or looped through for some other reason
                foreach (DataRow r in person.Rows)
                {
                    Person objPerson = new Person();
                    objPerson.setID(r["id"].ToString());
                    objPerson.setName(r[Properties.Settings.Default.PERSON_ATTRIB_NAME].ToString());
                    objPerson.setAddress(r[Properties.Settings.Default.PERSON_ATTRIB_ADDRESS].ToString());
                    listPersons.Add(objPerson);
                    break;
                }
                return listPersons[0];
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public DataTable GetAllPersons2GridView()
        {
            try
            {
                SQLiteDatabase db = new SQLiteDatabase();
                DataTable person = new DataTable();
                String query = "select id, " + Properties.Settings.Default.PERSON_ATTRIB_NAME + ", " + Properties.Settings.Default.PERSON_ATTRIB_ADDRESS;
                query += " from " + PERSONS_TABLE + ";";
                //MessageBox.Show(query);
                person = db.GetDataTable(query);
                return person;
            }
            catch (Exception fail)
            {
                String error = "The following error has occurred:\n\n";
                error += fail.Message.ToString() + "\r\n";
                error += fail.StackTrace.ToString() + "\r\n";
                MessageBox.Show(error);
                return null;
            }
        }

        public void Update(Person objPerson)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<String, String> data = new Dictionary<String, String>();
            //DataTable rows;
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_NAME, objPerson.getName());
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_ADDRESS, objPerson.getAddress());
            try
            {
                db.Update(PERSONS_TABLE, data, "id = " + objPerson.getID());
            }
            catch (Exception crap)
            {
                MessageBox.Show(crap.Message);
            }
            //db.

        }

        public void Insert(Person objPerson)
        {

            SQLiteDatabase db = new SQLiteDatabase();
            Dictionary<String, String> data = new Dictionary<String, String>();
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_NAME, objPerson.getName());
            data.Add(Properties.Settings.Default.PERSON_ATTRIB_ADDRESS, objPerson.getAddress());
            try
            {
                db.Insert(PERSONS_TABLE, data);
            }
            catch (Exception crap)
            {
                MessageBox.Show(crap.Message);
            }
        }

        public void Delete(String strPersonID)
        {
            SQLiteDatabase db = new SQLiteDatabase();
            db.Delete(PERSONS_TABLE, "id = "+strPersonID);
            db.Delete(PenaltyEvents.PENALTY_TABLE, "person_id = " + strPersonID);
        }

    }
}
